#!/usr/bin/env python
# -*- coding: utf-8 -*-
# SQL type wrappers audited by Joshua Kugler, 2008-02-20
"""
The tmpo_driver_unavailable table holds all the relevant records pertaining to
events that cause a driver to be 'unassignable to missions' for a specific
time and date range. This API manages all access to and modification of
data in the database.

    +----------------------+-----------------+--------------------------+
    |     Column Name      |       Type      |       Description        |
    +----------------------+-----------------+--------------------------+
    | du_id                | int,pk,identity | Unique record identifier |
    | driver_id            | int, fk         | driver_id, tmpo_drivers  |
    | description          | varchar         | Event description        |
    | type                 | int, fk         | du_type_id, tmpo_du_type |
    | du_start             | datetime        | Event start time/date    |
    | du_end               | datetime        | Event end time/date      |
    | comments             | text            | User notes for record    |
    +----------------------+-----------------+--------------------------+

Information from this table is referenced by some of the other APIs, through
the du_id key.

The functions found in this API are:

 get_all_events 
   selects all information, for all drivers out of the database, no filter

 get_event_data 
   selects all information for one event out of the database, using the
   du_id as the filter

 get_event_data_on_driver
   selects all event information out of the database, using the driver_id
   as the filter

 get_event_data_based_on_date_range
   selects data out of the database using start and end dates as the
   filter.
   
 get_driver_event_data_based_on_date_range
   selects data out of the database using both driver_id, and start/end
   dates as the filtering criteria.
   
 add_event
   inserts event data into the database, reading through a form for all the
   required information. Also takes into account 'conflicting entries', and
   flags them when required.
   
 update_event
   modifies database entries, using the du_id as the filter, reading a form
   for all the event information (modified or not). Doesn't assume that just
   because the original entry might not have been in conflict with something
   that the modified entry won't be, and accounts for this by flagging conflicts.
   
 delete_event
   removes event record from the database, using du_id as the filter. This is a
   physical removal, not a logical one.
"""
from string import Template
from tack.db import query, sql_date, sql_int, sql_string
from tack.lib import string_app, content_headers, status_codes, json_app
from tack.lib.dicttools import query_to_list
from commitment_conflict import set_to_conflicted_using_driver
import time

select_all_events = """
SELECT
    *
FROM
    tmpo_driver_unavailable
"""

select_event_data = """
SELECT
    *
FROM
    tmpo_driver_unavailable
WHERE
    du_id = $du_id
"""

select_event_data_on_driver = """
SELECT
    *
FROM
    tmpo_driver_unavailable
WHERE
    driver_id = $driver_id
ORDER BY du_start, du_end
"""

insert_event = """
INSERT INTO tmpo_driver_unavailable(
    driver_id,
    type,
    description,
    du_start,
    du_end,
    comments)
VALUES(
    $driver_id,
    $event_type,
    $description,
    $start,
    $finish,
    $comments)
"""

event_update = """
UPDATE
    tmpo_driver_unavailable
SET
    driver_id=$driver_id,
    type=$event_type,
    description=$description,
    du_start=$start,
    du_end=$finish,
    comments=$comments
WHERE
    du_id = $du_id
"""

event_delete = """
DELETE FROM 
    tmpo_driver_unavailable
WHERE
    du_id = $du_id
"""

select_event_data_based_on_date_range = """
SELECT
    *
FROM
    tmpo_driver_unavailable
WHERE
    (du_start <= '$start' 
      AND du_end >= '$finish') 
    OR (du_start >= '$start' 
      AND du_start <= '$finish') 
    OR (du_end >= '$start' 
      AND du_end <= '$finish')
"""
select_driver_event_data_based_on_date_range = """
SELECT
    *
FROM
    tmpo_driver_unavailable
WHERE
    (du_start <= '$start' 
      AND du_end >= '$finish') 
    OR (du_start >= '$start'
      AND du_start <= '$finish') 
    OR (du_end >= '$start'
      AND du_end <= '$finish') 
      AND (driver_id = $key)
"""


def get_all_events(environ, start_response):
    sql = select_all_events
    rows = query(environ['connection'], sql)
    output = {}
    labels = ['id','driver_id','description','type','start','end','comments']
    output['events'] = query_to_list(rows, labels)
    return json_app(output)(environ, start_response)

def get_event_data(environ, start_response):
    du_id = environ['dispatcher.groups']['du_id']
    sql = Template(select_event_data).substitute(locals())
    rows = query(environ['connection'], sql)
    output = {}
    labels = ['id','driver_id','description','type','start','end','comments']
    output['events'] = query_to_list(rows, labels)
    return json_app(output)(environ, start_response)

def get_event_data_on_driver(environ, start_response):
    driver_id = environ['dispatcher.groups']['driver_id']
    sql = Template(select_event_data_on_driver).substitute(locals())
    rows = query(environ['connection'], sql)
    output = {}
    labels = ['id','driver_id','description','type','start','end','comments']
    output['events'] = query_to_list(rows, labels)
    return json_app(output)(environ, start_response)

def get_event_data_based_on_date_range(environ, start_response):
    try:
        start = environ['dispatcher.groups']['start']
        throwaway = time.strptime(start, '%Y-%m-%d %H:%M')
        finish = environ['dispatcher.groups']['finish']
        throwaway = time.strptime(finish, '%Y-%m-%d %H:%M')
        sql = Template(select_event_data_based_on_date_range).substitute(locals())
        rows = query(environ['connection'], sql)
        output = {}
        labels = ['id','driver_id','description','type','start','end','comments']
        output['events'] = query_to_list(rows, labels)
    except:
        start_response(status_codes.error, content_headers.default_plain())
        return ['Error']
    return json_app(output)(environ, start_response)

def get_driver_event_data_based_on_date_range(environ, start_response):
    try:
        driver_id = environ['dispatcher.groups']['driver_id']
        start = environ['dispatcher.groups']['start']
        throwaway = time.strptime(start, '%Y-%m-%d %H:%M')
        finish = environ['dispatcher.groups']['finish']
        throwaway = time.strptime(finish, '%Y-%m-%d %H:%M')
        sql = Template(select_driver_event_data_based_on_date_range).substitute(locals())
        rows = query(environ['connection'], sql)
        output = {}
        labels = ['id','driver_id','description','type','start','end','comments']
        output['events'] = query_to_list(rows, labels)
    except:
        start_response(status_codes.error, content_headers.default_plain())
        return ['Error']
    return json_app(output)(environ, start_response)

def add_event(environ, start_response):
    try:
        form = environ['form']
        start = sql_date(form['start'], '%Y-%m-%d %H:%M')
        finish = sql_date(form['finish'], '%Y-%m-%d %H:%M')
        driver_id = sql_int(form['driver_id'])
        description = sql_string(form['description'])
        comments = sql_string(form['comments'])
        event_type = sql_int(form['event_type'])
        sql = Template(insert_event).substitute(locals())
        query(environ['connection'], sql)
        rows = query(environ['connection'], 'SELECT CAST(@@IDENTITY AS INT)')
        set_to_conflicted_using_driver(environ, driver_id, start, finish)
    except:
        start_response(status_codes.error, content_headers.default_plain())
        return ['Error']
    return json_app(rows)(environ, start_response)

def update_event(environ, start_response):
    try:
        form = environ['form']
        du_id = environ['dispatcher.groups']['du_id']
        driver_id = sql_int(form['driver_id'])
        description = sql_string(form['description'])
        event_type = sql_int(form['type'])
        comments = sql_string(form['comments'])
        start = sql_date(form['start'], '%Y-%m-%d %H:%M')
        finish = sql_date(form['finish'], '%Y-%m-%d %H:%M')
        sql = Template(event_update).substitute(locals())
        rows = query(environ['connection'], sql)
        set_to_conflicted_using_driver(environ, driver_id, start, finish)
        output = 'Event Updated'
    except:
        start_response(status_codes.error, content_headers.default_plain())
        return ['Error']
    return string_app(output)(environ, start_response)

def delete_event(environ, start_response):
    du_id = environ['dispatcher.groups']['du_id']
    sql = Template(event_delete).substitute(locals())
    rows = query(environ['connection'], sql)
    output = 'Event Deleted'
    return string_app(output)(environ, start_response)